﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace UtilsExtensionsAndHelpers.Utils.Implimentations
{
    public class MsSqlQuery : IMsSqlQuery
    {
        #region Fields
        private SqlConnection _connection;
        private SqlCommand _command;
        private SqlDataReader _reader;
        private bool _connectionClosed;
        private int _timeOut;
        private bool _usePersistentConnection;
        private readonly Dictionary<string, int> _columns = new Dictionary<string, int>();
        #endregion

        #region IDisposable
        public void Dispose()
        {

            if (_reader != null)
            {
                _reader.Dispose();
                _reader = null;
            }
            if (_command != null)
            {
                _command.Dispose();
                _command = null;
            }
            if (_connectionClosed && _connection != null)
            {
                _connection.Dispose();
                _connection = null;
            }
        }
        #endregion

        #region Constructors
        public MsSqlQuery(string connectionString) : this(connectionString, 0){}
        public MsSqlQuery(string connectionString, int timeOut) : this(connectionString, timeOut, false){}
        public MsSqlQuery(string connectionString, int timeOut, bool usePersistentConnection):this(new SqlConnection(connectionString), timeOut, usePersistentConnection){}
        public MsSqlQuery(SqlConnection connection):this(connection, 0){}
        public MsSqlQuery(SqlConnection connection, int timeOut):this(connection, timeOut, false){}
        public MsSqlQuery(SqlConnection connection, int timeOut, bool usePersistentConnection)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            _connection = connection;
            _timeOut = timeOut;
            _usePersistentConnection = usePersistentConnection;

        }
        protected MsSqlQuery(SqlConnection connection, string sql, int timeOut, CommandType commandType, IEnumerable<SqlParameter> parameters)
        {
            _connection = connection;
            _timeOut = timeOut;
            if (_connection == null) throw new ArgumentNullException("connection");
            if (sql == null) throw new ArgumentNullException("sql");

            try {
                _connectionClosed = false;
                _connection.Open();
            } catch {
                _connectionClosed = false;
            }

            _command = new SqlCommand(sql, _connection);
            _command.CommandType = commandType;
            _command.CommandTimeout = timeOut;

            if (parameters == null)
            {
                _reader = _command.ExecuteReader();
            }
            else
            {
                _command.Prepare();
                _command.Parameters.AddRange(parameters.ToArray());
                _reader = _command.ExecuteReader();
            }
        }
        #endregion

        #region Execute for one
        public T ExecuteSqlForOne<T>(string sql, Func<IRow, T> conversionAction)
        {
            return ExecuteSqlForOne(sql, null, conversionAction);
        }
        public T ExecuteSqlForOne<T>(string sql, IEnumerable<SqlParameter> parameters, Func<IRow, T> conversionAction)
        {
            var item = default(T);
            using (var q = new MsSqlQuery(_connection, sql, _timeOut, CommandType.Text, parameters))
            {
                while (q.Read())
                {
                    item = conversionAction(q);
                }
            }
            if (!_usePersistentConnection) {
                Dispose();
            }
            return item;
        }

        public T ExecuteProcedureForOne<T>(string procedureName, Func<IRow, T> conversionAction)
        {
            return ExecuteProcedureForOne(procedureName, null, conversionAction);
        }
        public T ExecuteProcedureForOne<T>(string procedureName, IEnumerable<SqlParameter> parameters, Func<IRow, T> conversionAction)
        {
            var item = default(T);
            using (var q = new MsSqlQuery(_connection, procedureName, _timeOut, CommandType.StoredProcedure, parameters))
            {
                while (q.Read())
                {
                    item = conversionAction(q);
                }
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
            return item;
        }
        #endregion  

        #region Execute for many
        public IEnumerable<T> ExecuteSqlForMany<T>(string sql, Func<IRow, T> conversionAction)
        {
            return ExecuteSqlForMany(sql, null, conversionAction);
        }
        public IEnumerable<T> ExecuteSqlForMany<T>(string sql, IEnumerable<SqlParameter> parameters, Func<IRow, T> conversionAction)
        {
            using (var q = new MsSqlQuery(_connection, sql, _timeOut, CommandType.Text, parameters))
            {
                while (q.Read())
                {
                    yield return conversionAction(q);
                }
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
        }

        public IEnumerable<T> ExecuteProcedureForMany<T>(string procedureName, Func<IRow, T> conversionAction)
        {
            return ExecuteProcedureForMany(procedureName, null, conversionAction);
        }
        public IEnumerable<T> ExecuteProcedureForMany<T>(string procedureName, IEnumerable<SqlParameter> parameters, Func<IRow, T> conversionAction)
        {
            using (var q = new MsSqlQuery(_connection, procedureName, _timeOut, CommandType.StoredProcedure, parameters))
            {
                while (q.Read())
                {
                    yield return conversionAction(q);
                }
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
        }
        #endregion

        #region Execute no result
        public void ExecuteSqlNoResult(string sql)
        {
            ExecuteSqlNoResult(sql, null);
        }
        public void ExecuteSqlNoResult(string sql, IEnumerable<SqlParameter> parameters)
        {
            using (var q = new MsSqlQuery(_connection, sql, _timeOut, CommandType.Text, parameters))
            {
                q.Read();
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
        }

        public void ExecuteProcedureNoResult(string procedureName)
        {
            ExecuteProcedureNoResult(procedureName, null);
        }
        public void ExecuteProcedureNoResult(string procedureName, IEnumerable<SqlParameter> parameters)
        {
            using (var q = new MsSqlQuery(_connection, procedureName, _timeOut, CommandType.Text, parameters))
            {
                q.Read();
            }
            if (!_usePersistentConnection)
            {
                Dispose();
            }
        }
        #endregion

        #region OrdinalColumn
        /// <summary>
        /// Returns the ordinal column number for a named column.
        /// The ordinal is cached so unlike SqlDataReader.GetOrdinal() it's not a performance hit to call it inside a loop.
        /// </summary>
        /// <param name="column"></param>
        /// <returns></returns>
        public int this[string column]
        {
            get
            {
                if (!_columns.ContainsKey(column))
                    _columns[column] = _reader.GetOrdinal(column);
                return _columns[column];
            }
        }
        #endregion

        #region SqlDataReader Pass-through

        public bool GetBoolean(string column)
        {
            return _reader.GetBoolean(this[column]);
        }

        public byte GetByte(string column)
        {
            return _reader.GetByte(this[column]);
        }

        public DateTime GetDateTime(string column)
        {
            return _reader.GetDateTime(this[column]);
        }

        public decimal GetDecimal(string column)
        {
            return _reader.GetDecimal(this[column]);
        }

        public double GetDouble(string column)
        {
            return _reader.GetDouble(this[column]);
        }

        public float GetFloat(string column)
        {
            return _reader.GetFloat(this[column]);
        }

        public short GetInt16(string column)
        {
            return _reader.GetInt16(this[column]);
        }

        public int GetInt32(string column)
        {
            return _reader.GetInt32(this[column]);
        }

        public long GetInt64(string column)
        {
            return _reader.GetInt64(this[column]);
        }

        public string GetString(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetString(this[column]);
        }

        public bool IsDBNull(string column)
        {
            return _reader.IsDBNull(this[column]);
        }

        public bool Read()
        {
            return _reader.Read();
        }

        #endregion

        #region GetNullable

        public bool? GetNullableBoolean(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetBoolean(this[column]);
        }

        public byte? GetNullableByte(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetByte(this[column]);
        }

        public DateTime? GetNullableDateTime(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetDateTime(this[column]);
        }

        public decimal? GetNullableDecimal(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetDecimal(this[column]);
        }

        public double? GetNullableDouble(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetDouble(this[column]);
        }

        public float? GetNullableFloat(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetFloat(this[column]);
        }

        public short? GetNullableInt16(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetInt16(this[column]);
        }

        public int? GetNullableInt32(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetInt32(this[column]);
        }

        public long? GetNullableInt64(string column)
        {
            if (_reader.IsDBNull(this[column]))
                return null;
            return _reader.GetInt64(this[column]);
        }

        #endregion
    }
}
